Bidirectional joins are used in circumstances where the data between 2 joined tables needs to flow in both directions. In a unidirectional join, the data flows in a single direction from the primary table (the table containing the primary key column) to the foreign table (the table containing the foreign key column).
A bidirectional join is used to enable the data to flow in both directions: the data can then flow from the primary table to the foreign table, and from the foreign table to the primary table. This allows users to aggregate a dimension column by a measure. This is in contrast to a typical unidirectional join, where the measure is aggregated by the dimension.
Bidirectional joins are typically not needed, as the database tends to be built with the relationships in mind. Users should take care when adding bidirectional joins, as they affect the aggregation of measures in the query.
Create a Bidirectional Join
To create a bidirectional join, click the join and enable 'Bidirectional' from the properties:
Example. Bidirectional Joins
In this example, a Count measure was added to the Product column. This column resides in the Products table, which is connected to the Data table via a unidirectional join on the ProductKey column.
The Product measure can be aggregated by the columns from the Products table. For instance, here the measure is aggregated by the Product dimension column, to show the count for each product:
Now let's switch out the Product column for the transactionID column from the Data table. Rather than seeing the product count for each id, we see the total products count in every row; the Product measure cannot be aggregated by the transactionID, because the data is flowing in the opposite direction (from the Products table to the Data table).
Now let's go back to Model and make the join between the Products and Data tables bidirectional:
Now when the query is re-run, we see the product count for each transaction id; the Product count has been aggregated by the transactionID column, because the bidirectional join allows the flow of data from the Data table to the Products table.
